﻿using System;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;
using sqlserverextensions.ExportOperations;


public partial class StoredProcedures
{
    [Microsoft.SqlServer.Server.SqlProcedure()]
    public static void QueryToXml(SqlString fileName,
                                  SqlString sqlStatement,
                                  SqlBoolean multiQuery,
                                  SqlBoolean columnHeader,
                                  SqlString rootNodeName)
    {


            //' Validieren der Übergabe-Parameter
            ExportHelper.ValidateMandatoryParameters(fileName, sqlStatement, multiQuery, columnHeader);
            if (rootNodeName.IsNull || String.IsNullOrEmpty(rootNodeName.ToString()))
                throw new SqlNullValueException("Null or empty rootNodeName");
            

            if (multiQuery)
            {
                DataSet dataSet = ExportHelper.GetDataSet(sqlStatement.ToString());

                Xml xml = new Xml(fileName.ToString(), rootNodeName.ToString());
                
                    xml.ElementStart("tables");

                    foreach(DataTable dt in dataSet.Tables)
                    {
                        xml.ElementStart("table");
                        xml.AttributeString("name", dt.TableName);
                        ExportHelper.Export2Xml(dt, ref xml, columnHeader.Value);
                        xml.ElementEnd();
                    }

                    xml.ElementEnd();

                    xml.Close();
                    xml.Dispose();

                }else{
                    using(SqlConnection connection  = new SqlConnection("context connection=true"))
                    {
                    connection.Open();

                    SqlDataReader dataReader = ExportHelper.GetDataReader(connection, sqlStatement.ToString());

                    Xml xml = new Xml(fileName.ToString(), rootNodeName.ToString());
                        xml.ElementStart("tables");
                        xml.ElementStart("table");
                        xml.AttributeString("name", "table");
                        ExportHelper.Export2Xml(ref dataReader, ref xml, columnHeader.Value);
                        xml.ElementEnd();
                        xml.ElementEnd();

                        xml.Close();
                    xml.Dispose();
                    dataReader.Close();
                    connection.Close();
                    }
                }

            }
};
